#!/bin/bash 
datadir=/var/lib/mysql
dbs=()
#声明变量
bak_dir=/home/mysql_backup/`date +%F`
pwd=123456root7890

#备份表数据及结构
backup()
{
 mysqldump -uroot -p$pwd -d $1 -R --triggers=false > $bak_dir/$1/$1.sql
 for i in `mysql -uroot -p$pwd --skip-column-names -e "use $1;show tables"`
 do
mysql -uroot -p$pwd <<EOU
use $1;
select * from $i into outfile '$bak_dir/$1/$i.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
EOU
 done
}


cd $datadir
for i in ${dbs[*]}
do
   if [ -d $i ]
   then
     mkdir -p $bak_dir/$i
     chown -R mysql:mysql $bak_dir/$i
     backup $i
   fi
done


#删除视图
rm -rf $bak_dir/activity/{cop_all_org.csv,cop_org.csv,cop_role.csv,cop_school.csv,cop_user.csv}
rm -rf $bak_dir/qbms/{all_org.csv,tab_chapter.csv,tab_grade.csv,tab_stage.csv,tab_subject.csv,tab_term.csv,tab_version.csv}
rm -rf $bak_dir/sbms/{v_deviceuser.csv,v_deviceuser_devicecode.csv}
rm -rf $bak_dir/ejabberd/apns_tokens.csv


#压缩
cd /home/mysql_backup
tar zcvf `date +%F`.tgz `date +%F`
rm -rf `date +%F`